<?php
include '../controller/Conexao.php';
include '../model/Proposta.php';
include '../model/Item.php';
include '../model/Subitem.php';
include '../model/ProdutoLista.php';
include '../model/Investimento.php';

class PropostaDAO{
	
	public function Insere(Proposta $proposta){
		try{
			mysql_query(  "INSERT INTO proposta( idcliente
											  , obra
											  , descricaoObra
											  , dateTime
											  , nomepdf)
									  VALUES ('".$proposta->getIdCliente()."'
									  		 ,'".$proposta->getObra()."'
									  		 ,'".$proposta->getDescricaoObra()."'
									  		 ,'".$proposta->getData()."'
									  		 ,'PC_".$proposta->getObra()."')" ) or die ("Insere:".mysql_error());
		 	
		}catch ( Exception $ex ){
			echo "Erro: ".$ex->getMessage();
		}
		
	}
	public static function getIdProposta(){
		try{
			$result = mysql_query("select max(idproposta) as idproposta from proposta") or die ("getIdProposta:".mysql_error());
			$row = mysql_fetch_assoc($result);
			return $row["idproposta"];
				
		}catch ( Exception $ex ){
			echo "Erro: ".$ex->getMessage();
		}	
	}
	public function insereLink(Proposta $proposta){
		try{
			$result = mysql_query("update proposta 
								      set link = '<a href=\"http://10.1.1.11:8080/Proposta Comercial/pdfs/Proposta_Comercial_".$proposta->getIdProposta().".pdf\">Download</a>'
									where idproposta =". $proposta->getIdProposta()) or die ("inserreLink:".mysql_error());
		 
		}catch ( Exception $ex ){
			echo "Erro: ".$ex->getMessage();
		}
	}
	
	public function insereVersao(Proposta $proposta){
		try{
			$result = mysql_query("update proposta
								      set versao = '".$proposta->getVersao()."'
									where idproposta =". $proposta->getIdProposta()) or die ("insereVersao:".mysql_error());
				
		}catch ( Exception $ex ){
			echo "Erro: ".$ex->getMessage();
		}
	}
	
	public static function getProposta($idProposta){
		try{
			$result = mysql_query("select * from proposta where idproposta = $idProposta order by dateTime desc") or die ("getProposta1:".mysql_error());
			$row = mysql_fetch_assoc($result);
			
			$proposta = new Proposta();
			$proposta->setIdProposta($row["idproposta"]);
			$proposta->setObra($row["obra"]);
			$proposta->setDescricaoObra($row["descricaoObra"]);
			$proposta->setData($row["dateTime"]);
			$proposta->setNomepdf($row["nomepdf"]);
			$proposta->setLink($row["link"]);
			$proposta->setIdCliente($row["idcliente"]);
			
			$result2 = mysql_query("select * from proposta pp,item it
									where pp.idproposta = it.idproposta
									  and  pp.idproposta = ".$row["idproposta"]) or die ("getProposta2:".mysql_error());
			
			while($row2 = mysql_fetch_array($result2)){
				$item = new Item();
				$item->setIdproposta($idProposta);
				$item->setNumero($row2["numero"]);
				$item->setTitulo($row2["titulo"]);
				
				$result3 = mysql_query("select * from item it,subitem st
										 where it.iditem = st.iditem
										   and it.iditem =".$row2["iditem"]) or die ("getProposta3:".mysql_error());
				while($row3 = mysql_fetch_array($result3)){
					$subitem = new Subitem();
					$subitem->setDescricao($row3["descricao"]);
					$subitem->setNumero($row3["numero"]);
					$subitem->setObs($row3["obs"]);
					
					$item->addSubitem($subitem);
				}	
				$proposta->addItem($item);
			}
			
			$result4 = mysql_query("select distinct numero,titulo 
					                  from investimento
							         where idproposta = ".$row["idproposta"]) or die ("getProposta2:".mysql_error());
				
			while($row4 = mysql_fetch_array($result4)){
				$inv = new Investimento();
				$inv->setIdproposta($idProposta);
				$inv->setNumero($row4['numero']);
				$inv->setTitulo($row4['titulo']);
			
				$result5 = mysql_query("select distinct descricao,round(total,2) as total 
										  from investimento 
										 where idproposta =".$row["idproposta"]) or die ("getProposta3:".mysql_error());
				
				while($row5 = mysql_fetch_array($result5)){
					$subitem = new Subitem();
					$subitem->setDescricao($row5["descricao"]);
					$subitem->setValorTotal($row5["total"]);
						
					$inv->addSubitem($subitem);
				}
				$proposta->setInvestimento($inv);
			}
			return $proposta;
		
		}catch ( Exception $ex ){
			echo "Erro: ".$ex->getMessage();
		}
	}
	
	public function Remove($id){
		try{
			mysql_query("delete from proposta where idproposta=$id") or die (mysql_error());
		    if(unlink("../pdfs/Proposta_Comercial_".$id.".pdf")){
		    	return true;
		    }
			//header("location:../view/ListaDevices.php");
		}catch ( Exception $ex ){
			echo "Erro: ".$ex->getMessage();
		}
	}
	
	public function Altera(Proposta $proposta){
		try{
			mysql_query(  "UPDATE proposta SET  idcliente		= 	'".$proposta->getIdCliente()."'
											  , obra 			= 	'".$proposta->getObra()."'
		 								      , descricaoObra 	= 	'".$proposta->getDescricaoObra()."'
											  , data			= 	'".$proposta->getData()."'
										WHERE idproposta =".$proposta->getIdProposta()) or die (mysql_error());
		}catch ( Exception $ex ){
			echo "Erro: ".$ex->getMessage();
		}
							
		//header("location:../view/ListaDevices.php");
	}
	
	public static function getDias($id){
		try{
			$rs = mysql_query("select sum(horas) as horas from item where idproposta = $id") or die (mysql_error());
			$horas = mysql_fetch_array($rs);
			
			return round(($horas["horas"] / 8),1,PHP_ROUND_HALF_UP);
		}catch ( Exception $ex ){
			echo "Erro: ".$ex->getMessage();
		}
			
		//header("location:../view/ListaDevices.php");
	}
	public static function Lista(){
		try{			
			$rs = mysql_query( 'select pp.idproposta
									 , (select nome from cliente where idcliente = pp.idcliente) as nome
									 , dateTime
									 , obra
									 , link
									 , totalCliente
									 , concat(\'<a href=\"http://10.1.1.11:8080/Proposta Comercial/view/resumo.php?idproposta=\',pp.idproposta,\'"\>Resumo</a>\') as resumo
									 , versao
 								  from proposta pp') or die(mysqli_error()) ;
			//'<a href=\"http://10.1.1.11:8080/Proposta Comercial/pdfs/Proposta_Comercial_".$proposta->getIdProposta().".pdf\">Download</a>'
			$result = array();
			while($row = mysql_fetch_object($rs)){
				array_push($result, $row);
			}	
			return $result;
		}catch ( Exception $ex ){
			echo "Erro: ".$ex->getMessage();
		}
	}
	public static function getPropostaById($idproposta){
		try{
			$rs = mysql_query( 'select pp.idproposta
									 , (select idcliente from cliente where idcliente = pp.idcliente) as idcliente							
									 , obra
 								  from proposta pp
								 where idproposta='.$idproposta) or die(mysqli_error()) ;
			//'<a href=\"http://10.1.1.11:8080/Proposta Comercial/pdfs/Proposta_Comercial_".$proposta->getIdProposta().".pdf\">Download</a>'
		
			$row = mysql_fetch_object($rs);
			
			return $row;
		}catch ( Exception $ex ){
			echo "Erro: ".$ex->getMessage();
		}
	}
	
	public function getValorTotal($idproposta){
		
		$res = mysql_query ( "select round(sum(parcialCliente),2)  as total
				from proposta pp
				,item it
				where pp.idproposta = it.idproposta
				and pp.idproposta =  $idproposta" );
		
		$result = mysql_fetch_array($res);
		return $result['total'];
	}
	
	public function getVersao($idproposta){
	
		$res = mysql_query ( "select count(1) as versao
								from proposta
							   where versao like concat ('%',(  select distinct trim(SUBSTRING_INDEX(versao, '/', 1)) as versao
																  from proposta
																 where idproposta = $idproposta) ,'%')" );
	
		$result = mysql_fetch_array($res);
		return $result['versao'];
	}
	
	public function getIdPropostaOrigem($idproposta){
	
		$res = mysql_query ( " select distinct trim(SUBSTRING_INDEX(versao, '/', 1)) as origem
								 from proposta
								where idproposta =  $idproposta" );
	
		$result = mysql_fetch_array($res);
		return $result['origem'];
	}
}	
	
?>


